package xujiwen.transport.Mgr;

import java.util.ArrayList;
import java.util.List;

import xujiwen.transport.bean.Station;
import xujiwen.transport.bean.User;
import xujiwen.transport.util.DBConnect;

public class StationMgr {
	public void Save(final Station station) {

		final DBConnect db = new DBConnect();
		final String sql = "insert into STATION values (null, ?, ?, ?, ?)";
		db.setAutoCommit(false);
		db.prepareStatement(sql);
		try {
			db.setInt(1, station.getLid());
			db.setString(2, station.getSName());
			db.setString(3, station.getAddress());
			db.setString(4, station.getPhone());
			db.excute();
			db.commit();

		} catch (final Exception ex) {
			ex.printStackTrace();
		} finally {
			db.setAutoCommit(true);
			db.close();
		}
	}
	
	public void Update(final Station station) {
		final DBConnect db = new DBConnect();

		final String sql = "update STATION set "
				+ "UID=?, SNAME=?, ADDRESS=?, PHONE=? "
				+ "where SID = ?";
		db.setAutoCommit(false);
		db.prepareStatement(sql);
		try {
			db.setInt(1, station.getLid());
			db.setString(2, station.getSName());
			db.setString(3, station.getAddress());
			db.setString(4, station.getPhone());
			db.setInt(5, station.getSid());
			db.excute();
			db.commit();
		} catch (final Exception ex) {
			ex.printStackTrace();
		} finally {
			db.setAutoCommit(true);
			db.close();
		}
	}
	
	public List<Station> GetSearchStationList(String str) {
		final List<Station> stations = new ArrayList<Station>();
		final DBConnect db = new DBConnect();
		final String sql;
		if(str.length() > 0)
		sql = "select USER.NAME,STATION.* from USER,STATION where STATION.UID=USER.UID and SNAME like '%" + str +"%' or ADDRESS like '%" + str +"%'";
		else sql = "select USER.NAME,STATION.* from USER,STATION where STATION.UID=USER.UID limit 10";
		//System.out.println(sql);
		db.prepareStatement(sql);
		db.excuteQuery();
		try {
			while (db.next()) {
				final Station station = new Station();
				station.setLeader(db.getString("NAME"));
				station.setSid(db.getInt("SID"));
				station.setLid(db.getInt("UID"));
				station.setSName(db.getString("SNAME"));
				station.setPhone(db.getString("PHONE"));
				station.setAddress(db.getString("ADDRESS"));
				stations.add(station);
			}
		} catch (final Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			db.close();
		}
		return stations;
	}
	
	public void Del(final int sid)
	{
		final DBConnect db = new DBConnect();
		final String sql = "delete from STATION where SID = ?";
		db.setAutoCommit(false);
		db.prepareStatement(sql);
		try {
			db.setInt(1, sid);
			db.excute();
			db.commit();
		} catch (final Exception ex) {
			ex.printStackTrace();
		} finally {
			db.setAutoCommit(true);
			db.close();
		}
	}

	public String GetStationByRelation(final int uid) {
		String sname=null;
		final DBConnect db = new DBConnect();
		final String sql = "select SNAME from STATION where UID = ?";
		db.prepareStatement(sql);
		db.setInt(1, uid);
		db.excuteQuery();
		try {
			while (db.next()) {
				sname = db.getString("SNAME");
			}
		} catch (final Exception ex) {
			ex.printStackTrace();
			return null;
		} finally {
			db.close();
		}
		return sname;
	}
}
